{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CH10 数据分组/数据透视表（菜品分类）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.1数据分组"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数据分组是根据一个或者多个键将数据分为若干组，将数据分成若干组<br>\n",
    "然后对分组后的数据进行汇总计算，最后将汇总计算后的结果进行合并<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10.1.1分组键是列名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户ID</th>\n",
       "      <th>客户分类</th>\n",
       "      <th>区域</th>\n",
       "      <th>是否省会</th>\n",
       "      <th>7月销量</th>\n",
       "      <th>8月销量</th>\n",
       "      <th>9月销量</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>59224</td>\n",
       "      <td>A类</td>\n",
       "      <td>一线城市</td>\n",
       "      <td>是</td>\n",
       "      <td>6</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>55295</td>\n",
       "      <td>B类</td>\n",
       "      <td>三线城市</td>\n",
       "      <td>否</td>\n",
       "      <td>37</td>\n",
       "      <td>27</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>46035</td>\n",
       "      <td>A类</td>\n",
       "      <td>二线城市</td>\n",
       "      <td>是</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2459</td>\n",
       "      <td>C类</td>\n",
       "      <td>一线城市</td>\n",
       "      <td>是</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22179</td>\n",
       "      <td>B类</td>\n",
       "      <td>三线城市</td>\n",
       "      <td>否</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    用户ID 客户分类    区域 是否省会  7月销量  8月销量  9月销量\n",
       "0  59224   A类  一线城市    是     6    20     0\n",
       "1  55295   B类  三线城市    否    37    27    35\n",
       "2  46035   A类  二线城市    是     8     1     8\n",
       "3   2459   C类  一线城市    是     7     8    14\n",
       "4  22179   B类  三线城市    否     9    12     4"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dict = {\"用户ID\":[59224,55295,46035,2459,22179],\n",
    "             \"客户分类\":[\"A类\",\"B类\",\"A类\",\"C类\",\"B类\"],\n",
    "             \"区域\":[\"一线城市\",\"三线城市\",\"二线城市\",\"一线城市\",\"三线城市\"],\n",
    "             \"是否省会\":[\"是\",\"否\",\"是\",\"是\",\"否\",],\n",
    "             \"7月销量\":[6,37,8,7,9],\n",
    "             \"8月销量\":[20,27,1,8,12],\n",
    "             \"9月销量\":[0,35,8,14,4]}\n",
    "df = pd.DataFrame(data_dict)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "按照一列进行分组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.groupby.DataFrameGroupBy object at 0x000001BCC230C828>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"客户分类\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "返回的是DataFrameGroupBy对象，而不是一个DataFrame对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户ID</th>\n",
       "      <th>区域</th>\n",
       "      <th>是否省会</th>\n",
       "      <th>7月销量</th>\n",
       "      <th>8月销量</th>\n",
       "      <th>9月销量</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>客户分类</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A类</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B类</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C类</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      用户ID  区域  是否省会  7月销量  8月销量  9月销量\n",
       "客户分类                                  \n",
       "A类       2   2     2     2     2     2\n",
       "B类       2   2     2     2     2     2\n",
       "C类       1   1     1     1     1     1"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"客户分类\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户ID</th>\n",
       "      <th>7月销量</th>\n",
       "      <th>8月销量</th>\n",
       "      <th>9月销量</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>客户分类</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A类</th>\n",
       "      <td>105259</td>\n",
       "      <td>14</td>\n",
       "      <td>21</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B类</th>\n",
       "      <td>77474</td>\n",
       "      <td>46</td>\n",
       "      <td>39</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C类</th>\n",
       "      <td>2459</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        用户ID  7月销量  8月销量  9月销量\n",
       "客户分类                          \n",
       "A类    105259    14    21     8\n",
       "B类     77474    46    39    39\n",
       "C类      2459     7     8    14"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"客户分类\").sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>用户ID</th>\n",
       "      <th>是否省会</th>\n",
       "      <th>7月销量</th>\n",
       "      <th>8月销量</th>\n",
       "      <th>9月销量</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>客户分类</th>\n",
       "      <th>区域</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">A类</th>\n",
       "      <th>一线城市</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>二线城市</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B类</th>\n",
       "      <th>三线城市</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C类</th>\n",
       "      <th>一线城市</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           用户ID  是否省会  7月销量  8月销量  9月销量\n",
       "客户分类 区域                                \n",
       "A类   一线城市     1     1     1     1     1\n",
       "     二线城市     1     1     1     1     1\n",
       "B类   三线城市     2     2     2     2     2\n",
       "C类   一线城市     1     1     1     1     1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby([\"客户分类\",\"区域\"]).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>用户ID</th>\n",
       "      <th>7月销量</th>\n",
       "      <th>8月销量</th>\n",
       "      <th>9月销量</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>客户分类</th>\n",
       "      <th>区域</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">A类</th>\n",
       "      <th>一线城市</th>\n",
       "      <td>59224</td>\n",
       "      <td>6</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>二线城市</th>\n",
       "      <td>46035</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B类</th>\n",
       "      <th>三线城市</th>\n",
       "      <td>77474</td>\n",
       "      <td>46</td>\n",
       "      <td>39</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C类</th>\n",
       "      <th>一线城市</th>\n",
       "      <td>2459</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            用户ID  7月销量  8月销量  9月销量\n",
       "客户分类 区域                           \n",
       "A类   一线城市  59224     6    20     0\n",
       "     二线城市  46035     8     1     8\n",
       "B类   三线城市  77474    46    39    39\n",
       "C类   一线城市   2459     7     8    14"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby([\"客户分类\",\"区域\"]).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "不过也仅对某一列进行分组计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "客户分类\n",
       "A类    2\n",
       "B类    2\n",
       "C类    1\n",
       "Name: 用户ID, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"客户分类\")[\"用户ID\"].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10.1.2分组键是Series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "将DataFrame中的某一列取出来就是一个Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    A类\n",
       "1    B类\n",
       "2    A类\n",
       "3    C类\n",
       "4    B类\n",
       "Name: 客户分类, dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"客户分类\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "给groupby()方法传入了什么，其他都一样，可以按照一个或多个Series进行分组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户ID</th>\n",
       "      <th>区域</th>\n",
       "      <th>是否省会</th>\n",
       "      <th>7月销量</th>\n",
       "      <th>8月销量</th>\n",
       "      <th>9月销量</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>客户分类</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A类</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B类</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C类</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      用户ID  区域  是否省会  7月销量  8月销量  9月销量\n",
       "客户分类                                  \n",
       "A类       2   2     2     2     2     2\n",
       "B类       2   2     2     2     2     2\n",
       "C类       1   1     1     1     1     1"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(df[\"客户分类\"]).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>用户ID</th>\n",
       "      <th>7月销量</th>\n",
       "      <th>8月销量</th>\n",
       "      <th>9月销量</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>客户分类</th>\n",
       "      <th>区域</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">A类</th>\n",
       "      <th>一线城市</th>\n",
       "      <td>59224</td>\n",
       "      <td>6</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>二线城市</th>\n",
       "      <td>46035</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B类</th>\n",
       "      <th>三线城市</th>\n",
       "      <td>77474</td>\n",
       "      <td>46</td>\n",
       "      <td>39</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C类</th>\n",
       "      <th>一线城市</th>\n",
       "      <td>2459</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            用户ID  7月销量  8月销量  9月销量\n",
       "客户分类 区域                           \n",
       "A类   一线城市  59224     6    20     0\n",
       "     二线城市  46035     8     1     8\n",
       "B类   三线城市  77474    46    39    39\n",
       "C类   一线城市   2459     7     8    14"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby([df[\"客户分类\"],df[\"区域\"]]).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "客户分类\n",
       "A类    2\n",
       "B类    2\n",
       "C类    1\n",
       "Name: 用户ID, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(df[\"客户分类\"])[\"用户ID\"].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10.1.3Aggregate方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户ID</th>\n",
       "      <th>客户分类</th>\n",
       "      <th>7月销量</th>\n",
       "      <th>8月销量</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>59224</td>\n",
       "      <td>A类</td>\n",
       "      <td>6</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>55295</td>\n",
       "      <td>B类</td>\n",
       "      <td>37</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>46035</td>\n",
       "      <td>A类</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2459</td>\n",
       "      <td>C类</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22179</td>\n",
       "      <td>B类</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    用户ID 客户分类  7月销量  8月销量\n",
       "0  59224   A类     6    20\n",
       "1  55295   B类    37    27\n",
       "2  46035   A类     8     1\n",
       "3   2459   C类     7     8\n",
       "4  22179   B类     9    12"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dict = {\"用户ID\":[59224,55295,46035,2459,22179],\n",
    "             \"客户分类\":[\"A类\",\"B类\",\"A类\",\"C类\",\"B类\"],\n",
    "             \"7月销量\":[6,37,8,7,9],\n",
    "             \"8月销量\":[20,27,1,8,12]}\n",
    "df = pd.DataFrame(data_dict)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Agggreate方法之所以神奇，是因为有如下两大特点<br>\n",
    "1.一次可以使用多种汇总方式<br>\n",
    "2.可以针对不同列进行不同的汇总运算<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">用户ID</th>\n",
       "      <th colspan=\"2\" halign=\"left\">7月销量</th>\n",
       "      <th colspan=\"2\" halign=\"left\">8月销量</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>sum</th>\n",
       "      <th>count</th>\n",
       "      <th>sum</th>\n",
       "      <th>count</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>客户分类</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A类</th>\n",
       "      <td>2</td>\n",
       "      <td>105259</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B类</th>\n",
       "      <td>2</td>\n",
       "      <td>77474</td>\n",
       "      <td>2</td>\n",
       "      <td>46</td>\n",
       "      <td>2</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C类</th>\n",
       "      <td>1</td>\n",
       "      <td>2459</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      用户ID          7月销量      8月销量    \n",
       "     count     sum count sum count sum\n",
       "客户分类                                  \n",
       "A类       2  105259     2  14     2  21\n",
       "B类       2   77474     2  46     2  39\n",
       "C类       1    2459     1   7     1   8"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"客户分类\").aggregate([\"count\",\"sum\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>用户ID</th>\n",
       "      <th colspan=\"2\" halign=\"left\">7月销量</th>\n",
       "      <th>8月销量</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>sum</th>\n",
       "      <th>count</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>客户分类</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A类</th>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B类</th>\n",
       "      <td>2</td>\n",
       "      <td>46</td>\n",
       "      <td>2</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C类</th>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      用户ID 7月销量       8月销量\n",
       "     count  sum count  sum\n",
       "客户分类                      \n",
       "A类       2   14     2   21\n",
       "B类       2   46     2   39\n",
       "C类       1    7     1    8"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"客户分类\").aggregate({\"用户ID\":\"count\",\"7月销量\":[\"sum\",\"count\"],\"8月销量\":\"sum\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10.1.4对分组后的结果重置索引"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "由于DataFrameGroupBy对象经过汇总运算后得到的形式不是标准的DataFrame形式，所以需要对分组结果进行转化<br>\n",
    "具体使用reset_index()方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户ID</th>\n",
       "      <th>7月销量</th>\n",
       "      <th>8月销量</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>客户分类</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A类</th>\n",
       "      <td>105259</td>\n",
       "      <td>14</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B类</th>\n",
       "      <td>77474</td>\n",
       "      <td>46</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C类</th>\n",
       "      <td>2459</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        用户ID  7月销量  8月销量\n",
       "客户分类                    \n",
       "A类    105259    14    21\n",
       "B类     77474    46    39\n",
       "C类      2459     7     8"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"客户分类\").sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>客户分类</th>\n",
       "      <th>用户ID</th>\n",
       "      <th>7月销量</th>\n",
       "      <th>8月销量</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A类</td>\n",
       "      <td>105259</td>\n",
       "      <td>14</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B类</td>\n",
       "      <td>77474</td>\n",
       "      <td>46</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C类</td>\n",
       "      <td>2459</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  客户分类    用户ID  7月销量  8月销量\n",
       "0   A类  105259    14    21\n",
       "1   B类   77474    46    39\n",
       "2   C类    2459     7     8"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"客户分类\").sum().reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.2数据透视表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数据透视表与分组不同之处在于，透视表是在行与列的方向上同时拆分<br>\n",
    "无论是Excel还是Python，这都是相当常见的作法，务必要牢记<br>\n",
    "pd.pivot_table(data, values(值) = , index(行) = , columns(列) = , aggfunc(计算类型) = , fill_value(对NA的填充) = ,<br>\n",
    "margins = , dropna(是否删除缺失值) = True , margins_name(合计列名) = )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户ID</th>\n",
       "      <th>客户分类</th>\n",
       "      <th>区域</th>\n",
       "      <th>是否省会</th>\n",
       "      <th>7月销量</th>\n",
       "      <th>8月销量</th>\n",
       "      <th>9月销量</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>59224</td>\n",
       "      <td>A类</td>\n",
       "      <td>一线城市</td>\n",
       "      <td>是</td>\n",
       "      <td>6</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>55295</td>\n",
       "      <td>B类</td>\n",
       "      <td>三线城市</td>\n",
       "      <td>否</td>\n",
       "      <td>37</td>\n",
       "      <td>27</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>46035</td>\n",
       "      <td>A类</td>\n",
       "      <td>二线城市</td>\n",
       "      <td>是</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2459</td>\n",
       "      <td>C类</td>\n",
       "      <td>一线城市</td>\n",
       "      <td>是</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22179</td>\n",
       "      <td>B类</td>\n",
       "      <td>三线城市</td>\n",
       "      <td>否</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    用户ID 客户分类    区域 是否省会  7月销量  8月销量  9月销量\n",
       "0  59224   A类  一线城市    是     6    20     0\n",
       "1  55295   B类  三线城市    否    37    27    35\n",
       "2  46035   A类  二线城市    是     8     1     8\n",
       "3   2459   C类  一线城市    是     7     8    14\n",
       "4  22179   B类  三线城市    否     9    12     4"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dict = {\"用户ID\":[59224,55295,46035,2459,22179],\n",
    "             \"客户分类\":[\"A类\",\"B类\",\"A类\",\"C类\",\"B类\"],\n",
    "             \"区域\":[\"一线城市\",\"三线城市\",\"二线城市\",\"一线城市\",\"三线城市\"],\n",
    "             \"是否省会\":[\"是\",\"否\",\"是\",\"是\",\"否\",],\n",
    "             \"7月销量\":[6,37,8,7,9],\n",
    "             \"8月销量\":[20,27,1,8,12],\n",
    "             \"9月销量\":[0,35,8,14,4]}\n",
    "df = pd.DataFrame(data_dict)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>区域</th>\n",
       "      <th>一线城市</th>\n",
       "      <th>三线城市</th>\n",
       "      <th>二线城市</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>客户分类</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A类</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B类</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C类</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "区域    一线城市  三线城市  二线城市\n",
       "客户分类                  \n",
       "A类     1.0   NaN   1.0\n",
       "B类     NaN   2.0   NaN\n",
       "C类     1.0   NaN   NaN"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, values = \"用户ID\", columns = \"区域\", index = \"客户分类\", aggfunc = \"count\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "因为Margin默认是为False的，所以如果想要合计的值，必须再手动改，默认列明为All，也可以手动再用margin_name去调整"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>区域</th>\n",
       "      <th>一线城市</th>\n",
       "      <th>三线城市</th>\n",
       "      <th>二线城市</th>\n",
       "      <th>合计</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>客户分类</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A类</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B类</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C类</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>合计</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "区域    一线城市  三线城市  二线城市  合计\n",
       "客户分类                      \n",
       "A类     1.0   NaN   1.0   2\n",
       "B类     NaN   2.0   NaN   2\n",
       "C类     1.0   NaN   NaN   1\n",
       "合计     2.0   2.0   1.0   5"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, values = \"用户ID\", columns = \"区域\", index = \"客户分类\", aggfunc = \"count\",\n",
    "               margins = True, margins_name = \"合计\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "通过fill_value参数，将空缺值填为0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>区域</th>\n",
       "      <th>一线城市</th>\n",
       "      <th>三线城市</th>\n",
       "      <th>二线城市</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>客户分类</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A类</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B类</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C类</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "区域    一线城市  三线城市  二线城市  All\n",
       "客户分类                       \n",
       "A类       1     0     1    2\n",
       "B类       0     2     0    2\n",
       "C类       1     0     0    1\n",
       "All      2     2     1    5"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, values = \"用户ID\", columns = \"区域\", index = \"客户分类\", aggfunc = \"count\",\n",
    "               margins = True, fill_value = 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果对不同的列有不同的计算方式，需要用字典的方式传入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">7月销量</th>\n",
       "      <th colspan=\"3\" halign=\"left\">用户ID</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>区域</th>\n",
       "      <th>一线城市</th>\n",
       "      <th>三线城市</th>\n",
       "      <th>二线城市</th>\n",
       "      <th>一线城市</th>\n",
       "      <th>三线城市</th>\n",
       "      <th>二线城市</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>客户分类</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A类</th>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B类</th>\n",
       "      <td>NaN</td>\n",
       "      <td>46.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C类</th>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     7月销量            用户ID          \n",
       "区域   一线城市  三线城市 二线城市 一线城市 三线城市 二线城市\n",
       "客户分类                               \n",
       "A类    6.0   NaN  8.0  1.0  NaN  1.0\n",
       "B类    NaN  46.0  NaN  NaN  2.0  NaN\n",
       "C类    7.0   NaN  NaN  1.0  NaN  NaN"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, values=[\"用户ID\", \"7月销量\"], columns = \"区域\",\n",
    "               index = \"客户分类\", aggfunc ={ \"用户ID\": \"count\", \"7月销量\": \"sum\" })"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "最后再用reset_index()方法对数据进行DataFrame化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>区域</th>\n",
       "      <th>客户分类</th>\n",
       "      <th>一线城市</th>\n",
       "      <th>三线城市</th>\n",
       "      <th>二线城市</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A类</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B类</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C类</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "区域 客户分类  一线城市  三线城市  二线城市\n",
       "0    A类   1.0   NaN   1.0\n",
       "1    B类   NaN   2.0   NaN\n",
       "2    C类   1.0   NaN   NaN"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df, values = \"用户ID\", columns = \"区域\", index = \"客户分类\", aggfunc = \"count\").reset_index()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
